package com.chen106106.sstest.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;

import com.chen106106.sstest.common.memcached.MemcachedCache;
import com.chen106106.sstest.model.User;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;

/**
 * 
 * @author chenz106106@gmail.com
 * 2012-7-15 下午7:02:55
 *
 */
public class UserMgrDaoImpl implements UserMgrDao {
	
	private NamedParameterJdbcTemplate jdbcTemplate;
	
	private Logger log = Logger.getLogger(UserMgrDaoImpl.class);
	private ParameterizedBeanPropertyRowMapper<User> rowMapper=ParameterizedBeanPropertyRowMapper.newInstance(User.class);
	
	private MemcachedCache defaultCache; 
	private static final String KEY = "usermgr-queryall";
	public void setJdbcTemplate(NamedParameterJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public long add(final User userInfo) {
		// TODO Auto-generated method stub
//		String sql = "INSERT INTO user(username,password) values(?,?)";
//		if(null==t){
//			return false;
//		}
//		log.info(sql);
//		boolean result = this.jdbcTemplate.getJdbcOperations().update(sql,t.getUsername(),t.getPassword())>0?true:false;
//		return result;
		if(null == userInfo){
			return -1;
		}

		long userId = -1;
		try {
			userId =  this.jdbcTemplate.getJdbcOperations().execute(new ConnectionCallback<Long>() {
				@Override
				public Long doInConnection(Connection conn) throws SQLException,DataAccessException {
					
					String sql = "INSERT INTO user(username,password) values(?,?)";					
					PreparedStatement ps=conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
					ps.setString(1, userInfo.getUsername());
					ps.setString(2,userInfo.getPassword());
					
					ps.executeUpdate();
					
					long userId = -1;
					ResultSet rs = ps.getGeneratedKeys();
			        if (rs.next()){
			        	userId = rs.getInt(1);
			        }
			        return userId;
				}
			});
		} catch (DataAccessException e) {
			log.error(e.getMessage(),e);
		}
		
		return userId;
	}

	@Override
	public boolean delete(int id) {
		//String sql = "DELETE FROM user WHERE id=?";
		String sql = "UPDATE user set deleted = 1 WHERE id=?";
		log.info(sql);
		boolean result = this.jdbcTemplate.getJdbcOperations().update(sql,id)>0?true:false;
		return result;
	}

	@Override
	public boolean update(User t) {
		String sql = "UPDATE user set username=?,password =? WHERE id=?";
		if(null==t){
			return false;
		}
		log.info(sql);
		boolean result = this.jdbcTemplate.getJdbcOperations().update(sql,t.getUsername(),t.getPassword(),t.getId())>0?true:false;
		return result;
	}

	@Override
	public User queryById(int id) {
		String sql = "SELECT id,username,password FROM user WHERE id=? limit 1";
		log.info(sql);
		User user = this.jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{id}, rowMapper);
		return user;
	}

	@Override
	public List<User> queryList(String field, Object value,int ...params) {
		String sql = "SELECT id,username,password FROM user ";
		StringBuilder sb = new StringBuilder();
		List<User>users = new ArrayList<User>();
		sb.append(sql);
		if(!StringUtils.isEmpty(field)){
			sb.append(" WHERE ");
			sb.append(field+"=?");
			users = this.jdbcTemplate.getJdbcOperations().query(sb.toString(),new Object[]{value} ,rowMapper);
		}
		if(params.length>0){
			int beginIndex = 0;
			int num = 0;
			if(params.length>1){
				beginIndex = params[0];
				num = params[1];
			}else{
				beginIndex = params[0];
				num = params[30];
			}
			sb.append("LIMIT ");
			sb.append(beginIndex);
			sb.append(" ,");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
			sb.append(" "+num);
			log.info(sb.toString());
		}
	
		users = this.jdbcTemplate.getJdbcOperations().query(sb.toString() ,rowMapper);
		return users;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<User> queryList(int ...params){
	    List<User> users = new ArrayList<User>();
//	    if (null != defaultCache.get(KEY)) {
//	    	return (List<User>) defaultCache.get(KEY);
//	    	} 
//	    else {
//	    	users = queryList("","",params);
//	    	//更新緩存 緩存失效時間為15分鐘
//	    	defaultCache.set(KEY, users, 15);
//	    }
	    users = queryList("","",params);
	    return users;
		
	}
	
	public void setDefaultCache(MemcachedCache defaultCache){
		this.defaultCache = defaultCache;
	}

	@Override
	public List<User> queryList(String field, Object value) {
		String sql = "SELECT id,username,password FROM user ";
		StringBuilder sb = new StringBuilder();
		List<User>users = new ArrayList<User>();
		sb.append(sql);
		if(!StringUtils.isEmpty(field)){
			sb.append(" WHERE ");
			sb.append(field+"=?");
			users = this.jdbcTemplate.getJdbcOperations().query(sb.toString(),new Object[]{value} ,rowMapper);
		}
		else{
			users = this.jdbcTemplate.getJdbcOperations().query(sb.toString() ,rowMapper);
		}
		return users;
	}

	@Override
	public int countUserNum() {
		try{
			String sql = "SELECT COUNT(id) FROM user WHERE deleted =0 ";
			return this.jdbcTemplate.getJdbcOperations().queryForInt(sql);
		}catch(Exception e){
			log.error(e.getMessage(),e);
			return 0;
		}
		
		
	}
	
}
